package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import base.Loadperson;
import base.Assignment;
import base.Bulletin;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;

//import javax.swing.JRadioButton;


public class DB_tools {

	public static Connection getConnection() throws Exception {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {

			e1.printStackTrace();
		}
		Connection connection = null;
		try {
			connection = DriverManager
					.getConnection("jdbc:mysql://192.168.9.4:3306/k530003036?"
							+ "user=k530003036&password=student");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}


public static void relaseSource(Connection conn ,Statement statement){
	if(statement!=null){
		try {
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	if(conn!=null){
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

public static void relaseSource(ResultSet rs,Connection conn ,Statement statement)
{
	if(rs != null){
		try {
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	DB_tools.relaseSource(conn, statement);
}

public static int signup(String Name,String password,String position, String email,String sex,String level)
{
	Connection conn = null;
	PreparedStatement statement=null;
ResultSet id=null;
 try{
 try {
	conn = DB_tools.getConnection(); 
} catch (SQLException e) {
	e.printStackTrace();
}
String sql="insert into staffinfo(name,password,position,email,sex,level) Values(?,?,?,?,?,?)";
statement = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
//statement = conn.prepareStatement(sql);
statement.setString(1, Name);
statement.setString(2,password);
statement.setString(3, position);
statement.setString(4,email);
statement.setString(5,sex);
statement.setString(6,level);
//statement.executeUpdate();
int result=statement.executeUpdate();
if(result!=0)
{
    ResultSet rs=statement.getGeneratedKeys();
    if(rs.next())
    {
       int Id=rs.getInt(1);
       return Id;
    }
}
}
catch(Exception e)
 {
	e.printStackTrace();
 }
 finally{
		DB_tools.relaseSource(conn, statement);
	}
 return 0;
}

public static int login(int id,String password){
	//System.out.println(name+password);
	Connection conn = null;
	  PreparedStatement statement = null;
	  ResultSet rs=null;
	 try{
	 try {
		conn = DB_tools.getConnection(); 
	} catch (SQLException e) {
		e.printStackTrace();
	}
	String sql="select * from staffinfo where id ='"+id+"' and password ='"+password+"'";
	statement = conn.prepareStatement(sql);
	rs=statement.executeQuery();
	//System.out.println(rs.getInt("id"));
	if(rs.first()){
		//System.out.println(rs.first());
		return 1;
	}
	}
	catch(Exception e)
	 {
		e.printStackTrace();
	 }
	 finally{
			DB_tools.relaseSource(rs,conn, statement);
		}
	 return 0;
}

public static Map getOneUser(String name, int id){
	//System.out.println(name+password);
	Map<String, String> row=new HashMap<String, String>();
	Connection conn = null;
	  PreparedStatement statement = null;
	  ResultSet rs=null;
	 try{
	 try {
		conn = DB_tools.getConnection(); 
	} catch (SQLException e) {
		e.printStackTrace();
	}
	String sql="select * from staffinfo where name ='"+name+"'and id='"+id+"'";
	statement = conn.prepareStatement(sql);
	rs=statement.executeQuery();
	//System.out.println(rs.getInt("id"));
	if(rs.next()){
		//System.out.println(rs.first());
		    //System.out.println(rs.getInt("id"));
		    int uid=rs.getInt("id");
			String first=String.valueOf(uid);
			row.put("id", first);
			row.put("name", rs.getString("name"));
			row.put("password", rs.getString("password"));
			row.put("position", rs.getString("position"));
			row.put("email", rs.getString("email"));
			row.put("image", rs.getString("image"));
			row.put("sex", rs.getString("sex"));
			row.put("level",rs.getString("level"));	
			System.out.println(row.get("id")+" "+row.get("name")+" "+row.get("password")+" "+row.get("position")+" "+row.get("email")+" "+row.get("imgae")+" "+row.get("sex")+" "+row.get("level"));
	}
	
	return row;
	}
	catch(Exception e)
	 {
		e.printStackTrace();
	 }
	 finally{
			DB_tools.relaseSource(conn, statement);
		}
	 return null;
}

public static void addBulletin(String content, String mname){
	Calendar now = Calendar.getInstance();  
    System.out.println("year: " + now.get(Calendar.YEAR));  
    System.out.println("month: " + (now.get(Calendar.MONTH) + 1) + "");  
    System.out.println("date: " + now.get(Calendar.DAY_OF_MONTH));  
    System.out.println(now.getTime());  
    Date d = new Date();  
    System.out.println(d);  
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
    String date = sdf.format(d);  
    System.out.println(date);  
	
	
	Connection conn = null;
	  PreparedStatement statement = null;
	 try{
	 try {
		conn = DB_tools.getConnection(); 
	} catch (SQLException e) {
		e.printStackTrace();
	}
	String sql="insert into bulletininfo(date,content,mname) Values(?,?,?)";
	statement = conn.prepareStatement(sql);
	statement.setString(1, date);
	statement.setString(2,content);
	statement.setString(3, mname);
	statement.executeUpdate();
	}
	catch(Exception e)
	 {
		e.printStackTrace();
	 }
	 finally{
			DB_tools.relaseSource(conn, statement);
		}
}

public static void addAssignment(String content,String sname, String mname, String id)
{
	 int Id=Integer.parseInt(id);
	 Date d = new Date();  
	 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
	 String date = sdf.format(d);  
	 Connection conn = null;
	 PreparedStatement statement = null;
	 try{
		 try{
			 conn=DB_tools.getConnection();
		 }catch(SQLException e)
		 {
			 e.printStackTrace();
		 }
		 String sql="insert into taskinfo(id,sname,mname,date,content) Values(?,?,?,?,?)";
		 statement=conn.prepareStatement(sql);
		 statement.setInt(1,Id);
		 statement.setString(2, sname);
		 statement.setString(3, mname);
		 statement.setString(4, date);
		 statement.setString(5, content);
		 statement.executeUpdate();
	 }catch(Exception e)
	 {
		 e.printStackTrace();
	 }finally{
		 DB_tools.relaseSource(conn, statement);
	 }
}

public static ObservableList<Bulletin> getBulletin(){
    ObservableList<Bulletin> personData = FXCollections.observableArrayList();
	Connection conn = null;
	  PreparedStatement statement = null;
	  ResultSet rs=null;
	 try{
	 try {
		conn = DB_tools.getConnection(); 
	} catch (SQLException e) {
		e.printStackTrace();
	}
	String sql="select * from  bulletininfo ";
	statement = conn.prepareStatement(sql);
	rs=statement.executeQuery();
	while(rs.next())
	 {
		personData.add(new Bulletin(rs.getString("content"),rs.getString("date"),rs.getString("mname")));
			 
		 }
	}
	catch(Exception e)
	 {
		e.printStackTrace();
	 }
	 finally{
			DB_tools.relaseSource(rs,conn, statement);
		}
	 return personData;
}

public static ObservableList<Assignment> getAssignment(int id)
{
	ObservableList<Assignment> personData = FXCollections.observableArrayList();
	Connection conn = null;
	PreparedStatement statement = null;
    ResultSet rs=null;
    String s;
    try{
    	try{
    	conn=DB_tools.getConnection();
    	}catch(SQLException e){
    		e.printStackTrace();
    	}
    	String sql="select * from  taskinfo where id=? ";
    	statement = conn.prepareStatement(sql);
    	statement.setInt(1, id);
    	rs=statement.executeQuery();
    	while(rs.next())
    	{
    		s=Integer.toString(rs.getInt("tid"));
    		personData.add(new Assignment(s,rs.getString("content"),rs.getString("date"),rs.getString("mname"),rs.getString("state")));
    	}
    }catch(Exception e){
    	e.printStackTrace();
    }finally{
    	DB_tools.relaseSource(rs, conn, statement);
    }
    return personData;
}

public static ObservableList<Assignment> getAssignment(String name)
{
	ObservableList<Assignment> personData = FXCollections.observableArrayList();
	Connection conn = null;
	PreparedStatement statement = null;
    ResultSet rs=null;
    String s;
    try{
    	try{
    	conn=DB_tools.getConnection();
    	}catch(SQLException e){
    		e.printStackTrace();
    	}
    	String sql="select * from  taskinfo where mname=? ";
    	statement = conn.prepareStatement(sql);
    	statement.setString(1, name);
    	rs=statement.executeQuery();
    	while(rs.next())
    	{
    		s=Integer.toString(rs.getInt("tid"));
    		personData.add(new Assignment(s,rs.getString("content"),rs.getString("date"),rs.getString("sname"),rs.getString("state")));
    	}
    }catch(Exception e){
    	e.printStackTrace();
    }finally{
    	DB_tools.relaseSource(rs, conn, statement);
    }
    return personData;
}

public static void finishAssignment(String tid) throws Exception
{
	Connection conn = null;
	PreparedStatement statement = null;
	String sql;
	String finish="finish";
	int assid=Integer.parseInt(tid);
	try{
		try
		{
			conn=DB_tools.getConnection();
		}catch(SQLException e){
			e.printStackTrace();
		}
		sql="update taskinfo set state=? where tid=?";
		statement=conn.prepareStatement(sql);
		statement.setString(1, finish);
		statement.setInt(2, assid);
		statement.executeUpdate();
	}catch(SQLException e){
		e.printStackTrace();
	}finally{
		DB_tools.relaseSource(conn, statement);
	}
}

public static int getIDFromSignUp(String name,String Password)
{
	Connection conn = null;
	  PreparedStatement statement = null;
	  ResultSet rs=null;
	  int userid=0;
	try{
		 try {
			conn = DB_tools.getConnection(); 
		} catch (SQLException e) {
			e.printStackTrace();
		}
		String sql="select id from staffinfo where name=? and password=?";
		statement = conn.prepareStatement(sql);
		statement.setString(1, name);
		statement.setString(2,Password);
		rs=statement.executeQuery();
		while(rs.next())
		{
			userid=rs.getInt("id");
			return userid;
		}

		}
		catch(Exception e)
		 {
			e.printStackTrace();
		 }finally{
			 DB_tools.relaseSource(rs,conn, statement);
		 }
	
	return 0;
}



public static void addBirth(int id,String name,int year,int month,String date)
{
	  Connection conn = null;
	  PreparedStatement statement = null;
	  //ResultSet rs=null;
	  Calendar cal=Calendar.getInstance();
	  int time=cal.get(Calendar.YEAR);
	  int Age=time-year;
	 try{
	 try {
		conn = DB_tools.getConnection(); 
	} catch (Exception e) {
		e.printStackTrace();
	}
	String sql="insert into birthinfo(id,name,year,month,date,age) Values(?,?,?,?,?,?)";
	statement = conn.prepareStatement(sql);
	statement.setInt(1, id);
	statement.setString(2,name);
	statement.setInt(3, year);
	statement.setInt(4,month);
	statement.setString(5,date);
	statement.setInt(6,Age);
	statement.executeUpdate();
	 }
	catch(SQLException e)
	 {
		e.printStackTrace();
	 }
	finally{
		DB_tools.relaseSource(conn, statement);
	}
}

public static String[][] gerUser() throws Exception{
	Connection connect = null;
	  Statement statement = null;
	  ResultSet resultSet=null;
	try {
		// This will load the MySQL driver, each DB has its own driver
		Class.forName("com.mysql.jdbc.Driver");
		// Setup the connection with the DB
		connect = DriverManager
				.getConnection("jdbc:mysql://localhost:3306/workshop?"
						+ "user=root&password=");
		String sql = "SELECT id, name from staffinfo";
		statement = connect.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

		resultSet = statement.executeQuery(sql);

		/*while (resultSet.next()){
			String id = resultSet.getString("id");
			String name = resultSet.getString("name");
			System.out.println(id +" "+ name);
		}*/
		resultSet.last();
		int row = resultSet.getRow();
		String namelist[][] = new String[row][2];
		resultSet.beforeFirst();
		int i = 0;
		while (resultSet.next()){
			String id = resultSet.getString("id");
			String name = resultSet.getString("name");
			int j = 0;
			namelist[i][j] = id;
			j += 1;
			namelist[i][j] = name;
			i++;
		}
		return namelist;
	} catch (Exception e) {
		throw e;
	} finally {
		 DB_tools.relaseSource(resultSet,connect, statement);
	}	

}


public static void deleteStaff(String Id) throws Exception
{
	 int id=Integer.parseInt(Id);
	 Connection conn1 = null;
	 Connection conn2 = null;
	 PreparedStatement statement1=null;
	 PreparedStatement statement2=null;
	 String sql1="delete from staffinfo where id=?";
	 String sql2="delete from birthinfo where id=?";
	try{
		try{
			 conn1=DB_tools.getConnection();
			 conn2=DB_tools.getConnection();
		 }
		 catch(SQLException e)
		 {
			 e.printStackTrace();
		 }
		 statement1 = conn1.prepareStatement(sql1);
		 statement1.setInt(1,id);
		 statement1.executeUpdate();
		 statement2 =conn2.prepareStatement(sql2);
		 statement2.setInt(1,id);
		 statement2.executeUpdate();
	}catch(SQLException e)
	{
		e.printStackTrace();
	}
	finally{
		DB_tools.relaseSource(conn1, statement1);
		DB_tools.relaseSource(conn2, statement2);
	}
}


 

 
 
 
 
 public static ObservableList<Loadperson> Namelist()
 {
	 ObservableList<Loadperson> personData = FXCollections.observableArrayList();
	 Connection conn1 = null;
	 Connection conn2 = null;
	 String a;
	 String level="staff";
	 PreparedStatement statement=null;
	 PreparedStatement statement1=null;
	 ResultSet rs=null;
	 ResultSet rs2=null;
	 try{
		 try{
			 conn1=DB_tools.getConnection();
			 conn2=DB_tools.getConnection();
		 }
		 catch(SQLException e)
		 {
			 e.printStackTrace();
		 }
	     String sql1="select * from qiandaoinfo where id=?";   
		 String sql="select * from staffinfo where level=?";
		 statement = conn1.prepareStatement(sql);
		 statement.setString(1,level);
		 rs=statement.executeQuery();
		 while(rs.next())
		 {
			 a=Integer.toString(rs.getInt("id"));
			 statement1=conn2.prepareStatement(sql1);
			 statement1.setInt(1, rs.getInt("id"));
			 rs2=statement1.executeQuery();
			 if(rs2.next())
			 { 
				 personData.add(new Loadperson(a,rs.getString("name"),rs.getString("position"),rs.getString("sex"),rs.getString("level"),rs2.getString("start")));
			 }
			 else
			 {
				 personData.add(new Loadperson(a,rs.getString("name"),rs.getString("position"),rs.getString("sex"),rs.getString("level"),"NULL"));
			 }
		 }
	 }
		 catch(Exception e)
		  {
		 	e.printStackTrace();
		  }	 
 finally{
 		DB_tools.relaseSource(rs,conn1, statement);
 		DB_tools.relaseSource(rs2,conn2, statement1);
 	}
	return personData;
 }

 public static ObservableList<Loadperson> getNamelist()
 {
	    ObservableList<Loadperson> personData = FXCollections.observableArrayList();
		Connection conn = null;
		  PreparedStatement statement = null;
		  ResultSet rs=null;
		 try{
		 try {
			conn = DB_tools.getConnection(); 
		} catch (SQLException e) {
			e.printStackTrace();
		}
		String sql="select * from  staffinfo";
		statement = conn.prepareStatement(sql);
		rs=statement.executeQuery();
		while(rs.next())
		 {
		    String a=Integer.toString(rs.getInt("id"));
			personData.add(new Loadperson(a,rs.getString("name")));
				 
			 }
		}
		catch(Exception e)
		 {
			e.printStackTrace();
		 }
		 finally{
				DB_tools.relaseSource(conn, statement);
			}
		 return personData;
	}

 public static int getworkdayinfo(String mon, int id){
		Calendar now = Calendar.getInstance();  
	    System.out.println("year: " + now.get(Calendar.YEAR));  
	    System.out.println("month: " + (now.get(Calendar.MONTH) + 1) + "");  
	    System.out.println("date: " + now.get(Calendar.DAY_OF_MONTH));  
	    System.out.println(now.getTime());  
	    Date d = new Date();  
	    System.out.println(d);  
	    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
	    String date = sdf.format(d);  
	    System.out.println(date); 
	    String year=String.valueOf(now.get(Calendar.YEAR));
		  
		  Connection conn = null;
		  PreparedStatement statement = null;
		  ResultSet rs=null;
		try{
			 try {
				conn = DB_tools.getConnection(); 
			} catch (SQLException e) {
				e.printStackTrace();
			}
			String sql="select num_present from attendance_statistics where id=? and month=? and year=?";
			statement = conn.prepareStatement(sql);
			//statement.setString(1, name);
			statement.setInt(1,id);
			statement.setString(2,mon);
			statement.setString(3,year);
			rs=statement.executeQuery();
			if(rs.first())
			{
				int num=rs.getInt("num_present");
				return num;
				
			}
			else return 0;

			}
			catch(Exception e)
			 {
				e.printStackTrace();
			 }finally{
				 DB_tools.relaseSource(rs,conn, statement);
			 }
		
		return 0;
	}

	public static int getoffdayinfo(String mon, int id){
		Calendar now = Calendar.getInstance();  
	    System.out.println("year: " + now.get(Calendar.YEAR));  
	    System.out.println("month: " + (now.get(Calendar.MONTH) + 1) + "");  
	    System.out.println("date: " + now.get(Calendar.DAY_OF_MONTH));  
	    System.out.println(now.getTime());  
	    Date d = new Date();  
	    System.out.println(d);  
	    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
	    String date = sdf.format(d);  
	    System.out.println(date); 
	    String year=String.valueOf(now.get(Calendar.YEAR));
		  
		  Connection conn = null;
		  PreparedStatement statement = null;
		  ResultSet rs=null;
		try{
			 try {
				conn = DB_tools.getConnection(); 
			} catch (SQLException e) {
				e.printStackTrace();
			}
			String sql="select num_offday from attendance_statistics where id=? and month=? and year=?";
			statement = conn.prepareStatement(sql);
			//statement.setString(1, name);
			statement.setInt(1,id);
			statement.setString(2,mon);
			statement.setString(3,year);
			rs=statement.executeQuery();
			if(rs.first())
			{
				int num=rs.getInt("num_offday");
				return num;
				
			}
			else return 0;

			}
			catch(Exception e)
			 {
				e.printStackTrace();
			 }finally{
				 DB_tools.relaseSource(rs,conn, statement);
			 }
		
		return 0;
	}
	
	//use to check attendance page
	public static void checkAttendance(String state, String name, int id){
		Connection conn1 = null;
		Connection conn2 = null;
		PreparedStatement statement1 = null;
		PreparedStatement statement2 = null;		//connect to database
		String work = "work";
		String offday = "offday";
		String xiaban = "xiaban";
		Calendar cal=Calendar.getInstance();
		String year = String.valueOf(cal.get(Calendar.YEAR));		//get the number of year
		String month = String.valueOf(cal.get(Calendar.MONTH) + 1);		//get the number of month, but because the value is from 0 to 11, so we need to plus 1
		String date = String.valueOf(cal.get(Calendar.DATE));
		String qiandao = DB_tools.judgeqiandao(name, id);		//use judgeqiandao function to get today's state of person
		System.out.println(qiandao);

	  	try{
	  		try {
	  			conn1 = DB_tools.getConnection(); 
	  			conn2 = DB_tools.getConnection(); 
	  		} catch (SQLException e) {
	  			e.printStackTrace();
	  		}
		
	  		if(state == work && qiandao == null){		//judge which button you click
	  			System.out.println("execute qiandao");
	  			String sql1 = "insert into qiandaoinfo(id, name, year, month, date, start) value (?,?,?,?,?,'work')";
	  			statement1 = conn1.prepareStatement(sql1);
	  			statement1.setInt(1, id);//id
	  			statement1.setString(2, name);//name
	  			statement1.setString(3, year);
	  			statement1.setString(4, month);
	  			statement1.setString(5, date);
	  			statement1.executeUpdate();
	  			DB_tools.relaseSource(conn1, statement1);
	  			
	  			try{
		  			String sql2 = "insert into attendance_statistics(id, year, month, name, num_present, num_offday) value (?, ?, ?, ?, '0', '0')";
		  			statement2 = conn2.prepareStatement(sql2);
		  			statement2.setInt(1, id);//id
		  			statement2.setString(2, year);
		  			statement2.setString(3, month);
		  			statement2.setString(4, name);//name
		  			statement2.executeUpdate();
		  			DB_tools.relaseSource(conn2, statement2);
	  			}
	  			catch(SQLException e){
	  				e.printStackTrace();
	  			}
	  			finally{
		  			String a = "num_present";		// connect to attendance_statistics database and change the value of it
		  			int number = DB_tools.getStatistic(a, name, id);
		  			System.out.println(number);
		  			number = number + 1;
		  			System.out.println(number);
		  			DB_tools.setStatistic(a, number, id);
	  			}
	  		}
	  		else if(state == offday && qiandao == null){
	  			System.out.println("execute offday");
	  			String sql = "insert into qiandaoinfo(id, name, year, month, date, start) value (?,?,?,?,?,'offday')";
	  			statement1 = conn1.prepareStatement(sql);
	  			statement1.setInt(1, id);//id
	  			statement1.setString(2, name);//name
	  			statement1.setString(3, year);
	  			statement1.setString(4, month);
	  			statement1.setString(5, date);
	  			statement1.executeUpdate();
	  			DB_tools.relaseSource(conn1, statement1);
	  			
	  			try{
		  			String sql2 = "insert into attendance_statistics(id, year, month, name, num_present, num_offday) value (?, ?, ?, ?, '0', '0')";
		  			statement2 = conn2.prepareStatement(sql2);
		  			statement2.setInt(1, id);//id
		  			statement2.setString(2, year);
		  			statement2.setString(3, month);
		  			statement2.setString(4, name);//name
		  			statement2.executeUpdate();
		  			DB_tools.relaseSource(conn2, statement2);
	  			}
	  			catch(SQLException e){
	  				e.printStackTrace();
	  			}
	  			finally{
	  				String a = "num_offday";
		  			int number = DB_tools.getStatistic(a, name, id);
		  			System.out.println(number);
		  			number = number + 1;
		  			System.out.println(number);
		  			DB_tools.setStatistic(a, number, id);
	  			}
	  		}
	  		else if(state == xiaban && qiandao.equals(work)){
	  			System.out.println("execute xiaban");
		  		String sql1 = "update qiandaoinfo set finish = ? where id=? and year=? and month=? and date=?";
		  		statement1 = conn1.prepareStatement(sql1);
		  		statement1.setString(1, xiaban);
		  		statement1.setInt(2, id);//鏀�
		  		statement1.setString(3, year);
		  		statement1.setString(4, month);
		  		statement1.setString(5, date);
		  		statement1.executeUpdate();
		  		DB_tools.relaseSource(conn1, statement1);
	  		}
	  	}
	  	catch(Exception e)
	  	{
	  		e.printStackTrace();
	  	}
	  	finally{
			DB_tools.relaseSource(conn1, statement1);
			DB_tools.relaseSource(conn2, statement2);
		}
	  	
	}
	//use to get the state of each people
	 public static String judgeqiandao(String name, int id){
		 Calendar cal=Calendar.getInstance();
		 String year = String.valueOf(cal.get(Calendar.YEAR));
		 String month = String.valueOf(cal.get(Calendar.MONTH) + 1);
		 String date = String.valueOf(cal.get(Calendar.DATE));
		 String judgeState = null;
		 Connection conn1 = null;
		 PreparedStatement statement1 = null;
		 ResultSet resultSet = null;
	  	 try{
	  		 try {
	  			 conn1 = DB_tools.getConnection(); 
	  		 } catch (SQLException e) {
	  			 e.printStackTrace();
	  		 }
	  		 String sql = "select * from qiandaoinfo where name=? and id=? and year=? and month=? and date=?";
	  		 statement1 = conn1.prepareStatement(sql);
	  		 statement1.setString(1, name);//name
	  		 statement1.setInt(2, id);//id
	  		 statement1.setString(3, year);
	  		 statement1.setString(4, month);
	  		 statement1.setString(5, date);
	  		 
	  		 resultSet = statement1.executeQuery();
	  		 if(resultSet.next()){
	  			 judgeState = resultSet.getString("start");
	  			 return judgeState;
	  		 }
	  		 
	  	 }
	  	 catch(Exception e){
	  		 e.printStackTrace();
	  	 }
	  	 finally{
	  		 DB_tools.relaseSource(conn1, statement1);
	  	 }
		return judgeState;
	}
	 
	 //get the statistics of each people
	 public static int getStatistic(String attribute, String name, int id){
		 Calendar cal = Calendar.getInstance();
		 String year = String.valueOf(cal.get(Calendar.YEAR));
		 String month = String.valueOf(cal.get(Calendar.MONTH) + 1);
		 Connection conn1 = null;
		 PreparedStatement statement1 = null;
		 ResultSet resultSet = null;
		 int number = 0;
	  	 try{
	  		 try {
	  			 conn1 = DB_tools.getConnection(); 
	  		 } catch (SQLException e) {
	  			 e.printStackTrace();
	  		 }
	  		 String sql = "select * from attendance_statistics where name=? and id=? and year=? and month=?";
	  		 statement1 = conn1.prepareStatement(sql);
	  		 statement1.setString(1, name);
	  		 statement1.setInt(2, id);
	  		 statement1.setString(3, year);
	  		 statement1.setString(4, month);
	  		 
	  		 resultSet = statement1.executeQuery();
	  		 if(resultSet.next()){
	  			 System.out.println(resultSet.getInt(attribute));
	  			 number = resultSet.getInt(attribute);
	  			 return number;
	  		 }
	  		 
	  	 }
	  	 catch(Exception e){
	  		 e.printStackTrace();
	  	 }
	  	 finally{
	  		 DB_tools.relaseSource(conn1, statement1);
	  	 }
		return number;
	  	 
	}
	 //use to change the value in database
	 public static void setStatistic(String attribute, int number, int id){
		 Calendar cal = Calendar.getInstance();
		 String year = String.valueOf(cal.get(Calendar.YEAR));
		 String month = String.valueOf(cal.get(Calendar.MONTH) + 1);
		 Connection conn1 = null;
		 PreparedStatement statement1 = null;
	  	 try{
	  		 try {
	  			 conn1 = DB_tools.getConnection(); 
	  		 } catch (SQLException e) {
	  			 e.printStackTrace();
	  		 }
	  		 String sql = "update attendance_statistics set " + attribute + " = ? where id=? and year=? and month=?";
	  		 /*String sql = "update attendance_statistics set num_present=? where id=? and year=? and month=?";*/
	  		 statement1 = conn1.prepareStatement(sql);
	  		 statement1.setInt(1, number);
	  		 statement1.setInt(2, id);
	  		 statement1.setString(3, year);
	  		 statement1.setString(4, month);
	  		 
	  		 statement1.executeUpdate();
	  		 
	  	 }
	  	 catch(Exception e){
	  		 e.printStackTrace();
	  	 }
	  	 finally{
	  		 DB_tools.relaseSource(conn1, statement1);
	  	 }
	  	 
	}
	 
	 public static String getNamefromId(int id,String Password)
	 {
	 	Connection conn = null;
	 	  PreparedStatement statement = null;
	 	  ResultSet rs=null;
	 	  //int userid=0;
	 	  String name;
	 	try{
	 		 try {
	 			conn = DB_tools.getConnection(); 
	 		} catch (SQLException e) {
	 			e.printStackTrace();
	 		}
	 		String sql="select name from staffinfo where id=? and password=?";
	 		statement = conn.prepareStatement(sql);
	 		statement.setInt(1, id);
	 		statement.setString(2,Password);
	 		rs=statement.executeQuery();
	 		while(rs.next())
	 		{
	 			name=rs.getString("name");
	 			return name;
	 		}

	 		}
	 		catch(Exception e)
	 		 {
	 			e.printStackTrace();
	 		 }finally{
	 			 DB_tools.relaseSource(rs,conn, statement);
	 		 }
	 	
	 	return null;
	 }
	 
	 public static boolean checkExist(int id, String name)
	 {
		//System.out.println(name+password);
			Connection conn = null;
			  PreparedStatement statement = null;
			  ResultSet rs=null;
			  //String id2 = String.valueOf(id);
			 try{
			 try {
				conn = DB_tools.getConnection(); 
			} catch (SQLException e) {
				e.printStackTrace();
			}
			String sql="select * from staffinfo where id = ? and name =?";
			statement = conn.prepareStatement(sql);
			statement.setInt(1, id);
			statement.setString(2, name);
			rs=statement.executeQuery();
			//System.out.println(rs.getInt("id"));
			if(rs.first()){
				//System.out.println(rs.first());
				return true;
			}
			}
			catch(Exception e)
			 {
				e.printStackTrace();
			 }
			 finally{
					DB_tools.relaseSource(rs,conn, statement);
				}
			 return false;
	 }


}



